package com.wu.Backstage.mapper;


import com.wu.Backstage.entity.Po.CommodityEntityPo;
import com.wu.Backstage.entity.UserAccount;
import org.apache.ibatis.annotations.*;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.sql.Date;
import java.sql.Time;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@Mapper
public interface UserMapper {

    /**查询数据库所有数据**/
    @Select("select * from useraccount")
    List<UserAccount> getAllUser();


    @Select("select * from useraccount where name=#{name}")
    UserAccount getAllUserByName(@Param("name") String name);

    @Select("select * from useraccount where email=#{email}")
    UserAccount getAllUserByEmail(@Param("email") String email);

    @Select("select * from useraccount where id = #{id}")
    UserAccount getAllUserByID(@Param("id") Integer id);

    @Select("select * from useraccount where email = #{email} and password = #{password}")
    UserAccount loginPage(String email,String password);

    @Insert("insert into useraccount"+
            "(email,password,name,date,time,statuscode,age,sex,dsc)"+
            "values " +
            "(#{email},#{password},#{name},#{date},#{time},#{statuscode},#{age},#{sex},#{dsc})")
    int logounPage(String email, String password, String name, Date date, Time time,Integer statuscode,Integer age,String sex,String dsc);

    @Update("update useraccount set statuscode=0 where email=#{email}")
    int upadteStat(String email);

    @Delete("delete from useraccount where id=#{id}")
    int DataleUsre(Integer id);

    @Update("update useraccount set email = #{email}," +
            "password = #{password}," +
            "name = #{name}," +
            "statuscode = #{statuscode}," +
            "age = #{age}," +
            "sex = #{sex} ," +
            "dsc = #{dsc}" +
            "where " +
            "id = #{id}")
    int UpdateUsre(String email,String name,String password,Integer statuscode,Integer age,String sex,String dsc,Integer id);



    @Update("update useraccount set password = #{password}," +
            "name = #{name}," +
            "age = #{age}," +
            "sex = #{sex} ," +
            "dsc = #{dsc}," +
            "img = #{img}"+
            "where " +
            "id = #{id}")
    int UserUpdateUsre(String name,String password,Integer age,String sex,String dsc,Integer id,String img);


    @Select("select t_id as id, t_dity_id as " +
            "dityId,t_name as mame,t_price as price," +
            "t_imgurl as imgurl," +
            "t_score as score " +
            "from tbl_commodity where t_state = #{sta} and t_sta =#{st}")
    ArrayList<HashMap<String,Object>> findCommodityEntityPoSp(Integer sta, Integer st);

    @Select("select sp.t_dity_id as tdityid,\n" +
            "sp.t_id as id,\n" +
            "sp.t_jointime as jointime,\n" +
            "sp.t_shoppingnum as shoppingnum,\n" +
            "sp.t_totalprice as totalprice,\n" +
            "sp.t_useremail as useremail,\n" +
            "cm.t_imgurl as mainimage,\n" +
            "cm.t_name as spname,\n" +
            "us.dsc as address,\n" +
            "cm.t_price as price\n" +
            "from tbl_shopping_cart sp \n" +
            "inner join tbl_commodity cm on sp.t_useremail=#{email} and  sp.t_dity_id = cm.t_dity_id\n" +
            "inner join  useraccount us where us.email = sp.t_useremail\n")
    ArrayList<HashMap<String,Object>> findshoppingCard(String email);


    @Select("select count(*) from tbl_shopping_cart where t_useremail = #{email}")
    int findCount(String email);



    @Select("SELECT * from tbl_order where tbl_order_user = #{email} LIMIT #{page} ,#{num}")
    ArrayList<HashMap<String,Object>> findOrder(String email,Integer page,Integer num);




    @Update("update tbl_commodity set t_count = t_count + 1  where t_id = #{id}")
    int ComCount(Integer id);


    @Select("SELECT *from tbl_commodity ORDER BY t_count desc LIMIT 0,8")
    ArrayList<HashMap<String,Object>> findCommCount();


    @Select("select av.t_useremail as useremail,\n" +
            "av.tbl_evaluatedtime as evaluatedtime,\n" +
            "av.t_textarea as textarea,\n" +
            "us.img as userimg,\n" +
            "us.name as username\n" +
            "from tbl_evaluated as av inner join useraccount as us on\n" +
            "av.t_useremail = us.email\n" +
            "inner join tbl_commodity as cm  on\n" +
            "cm.t_dity_id =  av.t_dity_id where av.t_dity_id = #{dityid}")
    ArrayList<HashMap<String,Object>> findevaluated (String dityid);


    @Select("SELECT *FROM tbl_commodity where t_sta = 0 and t_state = 1 and t_name LIKE  concat('%',#{name},'%') ")
    ArrayList<HashMap<String,Object>> finSearchProducts (String name);


}
